How to Convert Local Time to UTC

SQL Tutorial Home

Postgresql supports two timestamp data types: one of them includes a time zone indicator, and the other not. They are called:

Timestamp with time zone:

This is a point in time in a specific time zone. For example a value can be: “2022-04-17 11:25:25.931514-03” . The “-03” at the end indicates, this timestamp is in UTC-3 time zone.

Timestamp without time zone:

This is just a point in time. We don’t know in which time zone it is. However, conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using at time zone.

In postgres we can obtain the local current timestamp with:

Select now();
now();
2022-04-17 11:38:25.912141-03 (the data type of this value is timestamp with time zone)

We obtained the current timestamp in the localtime zone (which is represented by the configuration parameter TimeZone). In Postgres we can show the value of TimeZone with:

show TimeZone;
TimeZone
America/Buenos_Aires

To change the timezone of a timestamp with time zone data type value, we can use the AT clause, and specify the new time zone. As in the following example

with t1 as (		
select '2022-04-17 18:47:00.380122-03'::timestamp with time zone as created_at
Union
select '2022-04-17 12:15:00.380122-03'::timestamp with time zone	
)
Select created_at,
created_at AT time zone 'utc'
from t1;
created_at utc
2022-04-17 12:15:00.380122-03 2022-04-17 15:15:00.380122
2022-04-17 18:47:00.380122-03 2022-04-17 21:47:00.380122

You can use any time zone with the AT clause, for example time zone can converted to PST (pacific time zone) as we can see in the following query:

with t1 as (		
select '2022-04-17 18:47:00.380122-03'::timestamp with time zone as created_at
Union
select '2022-04-17 12:15:00.380122-03'::timestamp with time zone	
)
Select created_at,
created_at AT time zone 'utc',
created_at AT time zone 'pst'
from t1;
created_at utc pst
2022-04-17 12:15:00.380122-03 2022-04-17 15:15:00.380122 2022-04-17 07:15:00.380122
2022-04-17 18:47:00.380122-03 2022-04-17 21:47:00.380122 2022-04-17 13:47:00.380122

We can convert any timestamp without timezone to any specific time zone using the AT. For example if you have a timestamp without time zone column and you’re storing timestamps as UTC, you need to tell PostgreSQL that, and then tell it to convert it to any other time zone as in the following query:

with t1 as (		
select '2022-04-17 18:47:00.380122'::timestamp without time zone as created_at
Union
select '2022-04-17 12:15:00.380122'::timestamp without time zone	
)
Select	created_at at time zone 'America/Buenos_Aires' as Bue, 
created_at at time zone 'America/Buenos_Aires' at time zone 'america/los_angeles' AS la
from t1;
Bue la
2022-04-17 12:15:00.380122-03 2022-04-17 08:15:00.380122
2022-04-17 18:47:00.380122-03 2022-04-17 14:47:00.380122

IN THIS PAGE